package co.fitstart.admin.dao;

import java.util.Date;
import java.util.List;

import com.demo2do.core.persistence.annotation.Dynamic;
import com.demo2do.core.persistence.annotation.Execute;
import com.demo2do.core.persistence.annotation.Limit;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;

import co.fitstart.entity.coach.CoachRestCalendar;
import co.fitstart.entity.coach.CoachRestType;
import co.fitstart.entity.coach.CoachSettlement;
import co.fitstart.entity.coach.CoachSigninStatistic;
import co.fitstart.entity.coach.CoachStudentRelation;
import co.fitstart.entity.coach.PrincipalReportRelation;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.site.Site;

/**
 * 
 * @author lidan
 *
 */
@Dynamic("hibernate")
public interface CoachDao {
    
    /**
     * 
     * @param principal
     * @return
     */
    @Query("FROM CoachSettlement settlement WHERE settlement.principal = :coach")
    public List<CoachSettlement> listCoachSettlement(@Variable("coach") Principal principal);
    
    /**
     * 
     * @param coach
     * @return
     */
    @Limit
    @Query("FROM CoachStudentRelation WHERE coach = :coach")
    public List<CoachStudentRelation> listCoachStudentRelation(@Variable("coach") Principal coach, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param site
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE manager IS NOT NULL AND coach IS NOT NULL AND coach.disabled = false AND site = :site")
    public List<PrincipalReportRelation> listCoaches(@Variable("site") Site site); 
    
    /**
     * 
     * @param site
     * @param code
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE manager IS NOT NULL AND coach IS NOT NULL AND coach.disabled = false AND site = :site AND coach.profile.specialTags like :code")
    public List<PrincipalReportRelation> listCoaches(@Variable("site") Site site, @Variable("code") String code);

    /**
     * 
     * @param sites
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE manager IS NOT NULL AND coach IS NOT NULL AND coach.disabled = false AND site IN (:sites)")
    public List<PrincipalReportRelation> listCoaches(@Variable("sites") List<Site> sites); 
    
    
    @Query("FROM PrincipalReportRelation WHERE manager IS NOT NULL AND coach IS NOT NULL AND coach.disabled = false AND site IN (:sites) AND coach.profile.specialTags like :code")
    public List<PrincipalReportRelation> listCoaches(@Variable("sites") List<Site> sites, @Variable("code") String code); 

    /**
     * 
     * @param site
     * @return
     */
    @Query("SELECT relation.coach FROM PrincipalReportRelation relation WHERE relation.coach IS NOT NULL AND relation.coach.disabled = false AND relation.site = :site")
    public List<Principal> listSiteStaff(@Variable("site") Site site);
    
    /**
     * 
     * @param site
     * @return
     */
    @Query("SELECT coach.name FROM PrincipalReportRelation WHERE manager IS NOT NULL AND coach IS NOT NULL AND coach.disabled = false AND site = :site")
    public List<String> listCoachName(@Variable("site") Site site);

    /**
     * 
     * @param sites
     * @return
     */
    @Query("SELECT coach.name FROM PrincipalReportRelation WHERE manager IS NOT NULL AND coach IS NOT NULL AND coach.disabled = false AND sites IN (:sites)")
    public List<String> listCoachName(@Variable("sites") List<Site> sites);
    
    /**
     * 
     * @param manager
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NULL AND manager = :manager")
    public PrincipalReportRelation loadManager(@Variable("manager") Principal manager);
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE manager IS NULL AND coach = :coach")
    public PrincipalReportRelation loadSiteServer(@Variable("coach") Principal coach);
    
    /**
     * 
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NOT NULL AND coach.disabled = false AND manager IS NOT NULL")
    public List<PrincipalReportRelation> listCoaches();
    
    /**
     * 
     * @param code
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NOT NULL AND coach.disabled = false AND manager IS NOT NULL AND coach.profile.specialTags like :code")
    public List<PrincipalReportRelation> listCoaches(@Variable("code") String code);
    
    /**
     * 
     * @param manager
     * @return
     */
    @Query("SELECT principalReportRelation.coach.id FROM PrincipalReportRelation principalReportRelation WHERE principalReportRelation.coach IS NOT NULL AND principalReportRelation.manager = :manager")
    public List<Long> coacheIds(@Variable("manager") Principal manager);
    
    /**
     * 
     * @param sites
     * @return
     */
    @Query("SELECT principalReportRelation.coach.id FROM PrincipalReportRelation principalReportRelation WHERE principalReportRelation.coach IS NOT NULL AND principalReportRelation.site IN (:sites)")
    public List<Long> coacheIds(@Variable("sites") List<Site> sites);
    
    /**
     * 
     * @return
     */
    @Query("FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.coach = :coach AND coachRestCalendar.site = :site AND coach")
    public List<CoachRestCalendar> listCoachRestCalendar();
    
    
    /**
     * 
     * @param coach
     * @param restDate
     * @return
     */
    @Query("FROM CoachRestCalendar calendar WHERE calendar.disabled = false AND calendar.coach = :coach AND calendar.restDate = :restDate")
    public List<CoachRestCalendar> listRestHour(@Variable("coach") Principal coach, @Variable("restDate") Date restDate);
    
    /**
     * 
     * @param coach
     * @param restDate
     * @return
     */
    @Query("FROM CoachRestCalendar calendar WHERE calendar.disabled = false AND calendar.coach = :coach AND calendar.restDate = :restDate AND coachRestType in(2,3)")
    public List<CoachRestCalendar> listDutyOrBusinessHour(@Variable("coach") Principal coach, @Variable("restDate") Date restDate);
    /**
     * 
     * @param coach
     * @param restDate
     * @return
     */
    @Query("SELECT calendar.restHour FROM CoachRestCalendar calendar WHERE calendar.disabled = false AND calendar.coach = :coach AND calendar.restDate = :restDate")
    public List<String> listCoachRestHour(@Variable("coach") Principal coach, @Variable("restDate") Date restDate);
    
    /**
     * 
     * @param coach
     * @param restDate
     * @param coachRestType
     * @return
     */
    @Query("SELECT calendar.restHour FROM CoachRestCalendar calendar WHERE calendar.disabled = false AND calendar.coach = :coach AND calendar.restDate = :restDate AND calendar.coachRestType in ( :restTypes )")
    public List<String> listRestCalendar(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("restTypes")CoachRestType[] coachRestTypes);
    
    /**
     * 
     * @return
     */
    @Query("FROM PrincipalReportRelation relation WHERE relation.coach IS NOT NULL AND relation.manager IS NOT NULL AND relation.site IS NOT NULL")
    public List<PrincipalReportRelation> listCoachRelations();
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("SELECT relation.site FROM PrincipalReportRelation relation WHERE relation.coach IS NOT NULL AND relation.manager IS NOT NULL AND relation.coach = :coach")
    public Site getCoachRelation(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @param date
     * @param hour
     * @return
     */
    @Query("SELECT coachRestType FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :date AND restHour = :hour")
    public List<CoachRestType> getCoachRestCalendar(@Variable("coach") Principal coach, @Variable("date") Date date, @Variable("hour") String hour);
    
    /**
     * 
     * @param coach
     * @param date
     * @param hour
     * @return
     */
    @Query("SELECT coachRestType FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :date AND restHour in (:hour)")
    public List<CoachRestType> getCoachRestCalendar(@Variable("coach") Principal coach, @Variable("date") Date date, @Variable("hour") String[] hour);
    
    /**
     * 
     * @param coach
     * @param coachRestType
     * @param restDate
     * @param currentHour
     */
    @Execute("UPDATE CoachRestCalendar SET disabled = true WHERE coach = :coach AND coachRestType = :coachRestType AND restDate = :restDate AND restHour > :currentHour")
    public void deleteCoachRestCalendar(@Variable("coach") Principal coach, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restDate") Date restDate, @Variable("currentHour") String currentHour);

    /**
     * 
     * @param coach
     * @param coachRestType
     * @param restDate
     */
    @Execute("UPDATE CoachRestCalendar SET disabled = true WHERE coach = :coach AND coachRestType = :coachRestType AND restDate = :restDate")
    public void deleteCoachRestCalendar(@Variable("coach") Principal coach, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restDate") Date restDate);

    /**
     * 
     * @param coach
     * @param coachRestType
     * @param restDate
     */
    @Query("SELECT COUNT(*) FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND coachRestType = :coachRestType AND restDate = :restDate")
    public Long getCoachRestCalendarCount(@Variable("coach") Principal coach, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restDate") Date restDate);

    /**
     * 
     * @param coach
     * @return
     */
    @Query("SELECT relation.site FROM PrincipalReportRelation relation WHERE relation.manager IS NOT NULL AND relation.site IS NOT NULL AND relation.coach = :coach")
    public Site getCoachSite(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @param dayOfWeek
     * @return
     */
    @Query("SELECT relation.site FROM PrincipalReportRelation relation WHERE relation.site.disabled = false AND relation.manager IS NOT NULL AND relation.site IS NOT NULL AND relation.coach = :coach AND (relation.appointDays IS NULL OR relation.appointDays LIKE :dayOfWeek)")
    public List<Site> listCoachSite(@Variable("coach") Principal coach, @Variable("dayOfWeek") String dayOfWeek);
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("SELECT relation.site FROM PrincipalReportRelation relation WHERE relation.site.disabled = false AND relation.manager IS NOT NULL AND relation.site IS NOT NULL AND relation.coach = :coach")
    public List<Site> listCoachSite(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @param month
     * @return
     */
    @Query("FROM CoachSigninStatistic WHERE principal = :coach AND month = :month")
    public CoachSigninStatistic loadCoachSigninStatistic(@Variable("coach") Principal coach, @Variable("month") String month);
}
